package com.wontube.smr.dao.providers;

import static org.apache.ibatis.jdbc.SqlBuilder.BEGIN;
import static org.apache.ibatis.jdbc.SqlBuilder.FROM;
import static org.apache.ibatis.jdbc.SqlBuilder.INNER_JOIN;
import static org.apache.ibatis.jdbc.SqlBuilder.LEFT_OUTER_JOIN;
import static org.apache.ibatis.jdbc.SqlBuilder.ORDER_BY;
import static org.apache.ibatis.jdbc.SqlBuilder.SELECT;
import static org.apache.ibatis.jdbc.SqlBuilder.SELECT_DISTINCT;
import static org.apache.ibatis.jdbc.SqlBuilder.SQL;

import java.util.Map;

import com.wontube.core.dao.mybatis.QueryExample;
import com.wontube.core.dao.mybatis.provider.SqlProvider;


/**
 * @author yuechao
 * 
 */
public final class MovieSqlProvider extends SqlProvider {
	public String selectWithRecent(QueryExample example) {
		BEGIN();
		if (example != null && example.isDistinct()) {
			SELECT_DISTINCT("movie.id");
		} else {
			SELECT("movie.id");
		}

		SELECT("movie.banner_img_url, movie.movie_type, movie.sort_index, movie.area, movie.version");
		SELECT("movie.video_id, movie.item_url, movie.genre, movie.score, movie.publish_time");
		SELECT("movie.actors, movie.director, movie.website, movie.duration, movie.view_count");
		SELECT("movie.uploader, movie.description, movie.thumbnail, movie.title,movie.parent_title");

		FROM("movie");
		LEFT_OUTER_JOIN("recent_movie on movie.id = recent_movie.movie_id");

		applyWhere(example, false);

		if (example != null && example.getOrderByClause() != null) {
			ORDER_BY(example.getOrderByClause());
		}

		return SQL();
	}

	public String selectWithToday(QueryExample example) {
		BEGIN();
		if (example != null && example.isDistinct()) {
			SELECT_DISTINCT("movie.id");
		} else {
			SELECT("movie.id");
		}

		SELECT("movie.banner_img_url, movie.movie_type, movie.sort_index, movie.area, movie.version");
		SELECT("movie.video_id, movie.item_url, movie.genre, movie.score, movie.publish_time");
		SELECT("movie.actors, movie.director, movie.website, movie.duration, movie.view_count");
		SELECT("movie.uploader, movie.description, movie.thumbnail, movie.title,movie.parent_title");

		FROM("movie");
		LEFT_OUTER_JOIN("movie_today on movie.id = movie_today.movie_id");

		applyWhere(example, false);

		if (example != null && example.getOrderByClause() != null) {
			ORDER_BY(example.getOrderByClause());
		}

		return SQL();
	}

	public String selectWithTodayByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");

		BEGIN();
		if (example != null && example.isDistinct()) {
			SELECT_DISTINCT("movie.id");
		} else {
			SELECT("movie.id");
		}

		SELECT("movie.banner_img_url, movie.movie_type, movie.sort_index, movie.area, movie.version");
		SELECT("movie.video_id, movie.item_url, movie.genre, movie.score, movie.publish_time");
		SELECT("movie.actors, movie.director, movie.website, movie.duration, movie.view_count");
		SELECT("movie.uploader, movie.description, movie.thumbnail, movie.title,movie.parent_title");

		FROM("movie");

		LEFT_OUTER_JOIN("movie_today on movie.id = movie_today.movie_id");
		INNER_JOIN("movie_genre on movie.id = movie_genre.movie_id");

		String genreName = (String) map.get("genreName");
		applyInCondition("movie_genre.genre_name", genreName);

		applyWhere(example, true);

		if (example != null && example.getOrderByClause() != null) {
			ORDER_BY(example.getOrderByClause());
		}

		String sql = SQL();
		return sql;
	}

	public String selectWithWeek(QueryExample example) {
		BEGIN();
		if (example != null && example.isDistinct()) {
			SELECT_DISTINCT("movie.id");
		} else {
			SELECT("movie.id");
		}

		SELECT("movie.banner_img_url, movie.movie_type, movie.sort_index, movie.area, movie.version");
		SELECT("movie.video_id, movie.item_url, movie.genre, movie.score, movie.publish_time");
		SELECT("movie.actors, movie.director, movie.website, movie.duration, movie.view_count,movie.parent_title");
		SELECT("movie.uploader, movie.description, movie.thumbnail, movie.title");

		FROM("movie");
		LEFT_OUTER_JOIN("movie_week on movie.id = movie_week.movie_id");

		applyWhere(example, false);

		if (example != null && example.getOrderByClause() != null) {
			ORDER_BY(example.getOrderByClause());
		}
		
		String sql = SQL();
		return sql;
	}

	public String selectWithWeekByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");

		BEGIN();
		if (example != null && example.isDistinct()) {
			SELECT_DISTINCT("movie.id");
		} else {
			SELECT("movie.id");
		}

		SELECT("movie.banner_img_url, movie.movie_type, movie.sort_index, movie.area, movie.version");
		SELECT("movie.video_id, movie.item_url, movie.genre, movie.score, movie.publish_time");
		SELECT("movie.actors, movie.director, movie.website, movie.duration, movie.view_count,movie.parent_title");
		SELECT("movie.uploader, movie.description, movie.thumbnail, movie.title");

		FROM("movie");

		LEFT_OUTER_JOIN("movie_week on movie.id = movie_week.movie_id");
		INNER_JOIN("movie_genre on movie.id = movie_genre.movie_id");

		String genreName = (String) map.get("genreName");
		applyInCondition("movie_genre.genre_name", genreName);

		applyWhere(example, true);

		if (example != null && example.getOrderByClause() != null) {
			ORDER_BY(example.getOrderByClause());
		}

		return SQL();
	}

	public String countWithRecent(QueryExample example) {
		BEGIN();

		SELECT("count(*)");
		FROM("movie");
		LEFT_OUTER_JOIN("recent_movie on movie.id = recent_movie.movie_id");

		applyWhere(example, false);

		return SQL();
	}

	public String selectWithRecentByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");

		BEGIN();
		if (example != null && example.isDistinct()) {
			SELECT_DISTINCT("movie.id");
		} else {
			SELECT("movie.id");
		}

		SELECT("movie.banner_img_url, movie.movie_type, movie.sort_index, movie.area, movie.version");
		SELECT("movie.video_id, movie.item_url, movie.genre, movie.score, movie.publish_time");
		SELECT("movie.actors, movie.director, movie.website, movie.duration, movie.view_count,movie.parent_title");
		SELECT("movie.uploader, movie.description, movie.thumbnail, movie.title");

		FROM("movie");

		LEFT_OUTER_JOIN("recent_movie on movie.id = recent_movie.movie_id");
		INNER_JOIN("movie_genre on movie.id = movie_genre.movie_id");

		String genreName = (String) map.get("genreName");
		applyInCondition("movie_genre.genre_name", genreName);

		applyWhere(example, true);

		if (example != null && example.getOrderByClause() != null) {
			ORDER_BY(example.getOrderByClause());
		}

		return SQL();
	}

	public String countWithRecentByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");

		BEGIN();

		SELECT("count(*)");
		FROM("movie");

		LEFT_OUTER_JOIN("recent_movie on movie.id = recent_movie.movie_id");
		INNER_JOIN("movie_genre on movie.id = movie_genre.movie_id");

		String genreName = (String) map.get("genreName");
		applyInCondition("movie_genre.genre_name", genreName);

		applyWhere(example, true);

		String sql = SQL();

		return sql;
	}
	public String countWithDayByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");
		
		BEGIN();
		
		SELECT("count(*)");
		FROM("movie");
		
		LEFT_OUTER_JOIN("movie_today on movie.id = movie_today.movie_id");
		INNER_JOIN("movie_genre on movie.id = movie_genre.movie_id");
		
		String genreName = (String) map.get("genreName");
		applyInCondition("movie_genre.genre_name", genreName);
		
		applyWhere(example, true);
		
		String sql = SQL();
		
		return sql;
	}
	public String countWithWeekByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");
		
		BEGIN();
		
		SELECT("count(*)");
		FROM("movie");
		
		LEFT_OUTER_JOIN("movie_week on movie.id = movie_week.movie_id");
		INNER_JOIN("movie_genre on movie.id = movie_genre.movie_id");
		
		String genreName = (String) map.get("genreName");
		applyInCondition("movie_genre.genre_name", genreName);
		
		applyWhere(example, true);
		
		String sql = SQL();
		
		return sql;
	}

	public String countWithToday(QueryExample example) {
		BEGIN();

		SELECT("count(*)");
		FROM("movie");
		LEFT_OUTER_JOIN("movie_today on movie.id = movie_today.movie_id");

		applyWhere(example, false);

		return SQL();
	}

	public String countWithWeek(QueryExample example) {
		BEGIN();

		SELECT("count(*)");
		FROM("movie");
		LEFT_OUTER_JOIN("movie_week on movie.id = movie_week.movie_id");

		applyWhere(example, false);

		return SQL();
	}

	public String selectWithByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");

		BEGIN();
		if (example != null && example.isDistinct()) {
			SELECT_DISTINCT("movie.id");
		} else {
			SELECT("movie.id");
		}

		SELECT("movie.banner_img_url, movie.movie_type, movie.sort_index, movie.area, movie.version");
		SELECT("movie.video_id, movie.item_url, movie.genre, movie.score, movie.publish_time");
		SELECT("movie.actors, movie.director, movie.website, movie.duration, movie.view_count");
		SELECT("movie.uploader, movie.description, movie.thumbnail, movie.title,movie.parent_title");

		FROM("movie");

		INNER_JOIN("movie_genre on movie.id = movie_genre.movie_id");

		String genreName = (String) map.get("genreName");
		applyInCondition("movie_genre.genre_name", genreName);
		applyWhere(example, true);

		if (example != null && example.getOrderByClause() != null) {
			ORDER_BY(example.getOrderByClause());
		}

		return SQL();
	}

	public String countWithByGenre(Map<String, Object> map) {
		QueryExample example = (QueryExample) map.get("example");

		BEGIN();

		SELECT("count(*)");
		FROM("movie");

		INNER_JOIN("movie_genre on movie.id = movie_genre.movie_id");

		String genreName = (String) map.get("genreName");
		applyInCondition("movie_genre.genre_name", genreName);

		applyWhere(example, true);

		return SQL();
	}
}
